package com.hngy.xpq.android_practice.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.hngy.xpq.android_practice.bean.Comm;
import com.hngy.xpq.android_practice.utils.CommOrder;

import java.util.ArrayList;
import java.util.List;

public class SQLiteComm extends SQLiteOpenHelper {     //<商品>
    SQLiteDatabase sqLiteDatabase;

    public SQLiteComm(Context context) {
        super(context, CommOrder.DATABASE_DATA, null, CommOrder.DATABASE_INS);
        sqLiteDatabase = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + CommOrder.DATABASE_TABLE + "("
                + CommOrder.NOTEPAD_ID + " integer primary key autoincrement,"
                + CommOrder.NOTEPAD_NAME + " VARCHAR(25),"
                + CommOrder.NOTEPAD_MER_NAME + " VARCHAR(50),"
                + CommOrder.NOTEPAD_TYPES + " VARCHAR(20),"
                + CommOrder.NOTEPAD_PRICE + " VARCHAR(10),"
                + CommOrder.NOTEPAD_PHONE + " VARCHAR(30),"
                + CommOrder.NOTEPAD_TIME + " VARCHAR(20),"
                + CommOrder.NOTEPAD_DES + " VARCHAR(200),"
                + CommOrder.NOTEPAD_STATUS + " VARCHAR(10),"
                + "image BLOB)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public boolean Comm_add(Comm c) {
        ContentValues values = new ContentValues();
        values.put(CommOrder.NOTEPAD_NAME, c.getComm_name());
        values.put(CommOrder.NOTEPAD_MER_NAME, c.getMerchant_name());
        values.put(CommOrder.NOTEPAD_TYPES, c.getComm_types());
        values.put(CommOrder.NOTEPAD_PRICE, c.getComm_price());
        values.put(CommOrder.NOTEPAD_PHONE, c.getComm_phone());
        values.put(CommOrder.NOTEPAD_TIME, c.getComm_time());
        values.put(CommOrder.NOTEPAD_DES, c.getComm_des());
        values.put(CommOrder.NOTEPAD_STATUS, c.getStatus());
        values.put("image", c.getImage());
        return
                sqLiteDatabase.insert(CommOrder.DATABASE_TABLE, null, values) > 0;
    }

    public List<Comm> CommMerchantQuery(String name) {             //买家通过商家名查找商品信息
        List<Comm> list = new ArrayList<Comm>();
        try {
            Cursor cursor = sqLiteDatabase.rawQuery("select * from " + CommOrder.DATABASE_TABLE + " where " + CommOrder.NOTEPAD_MER_NAME + " = ?", new String[]{ name }, null);
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    Comm c = new Comm();
                    String id = cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_ID));
                    int ids = Integer.valueOf(id);
                    c.setId(ids);
                    c.setImage(cursor.getBlob(cursor.getColumnIndex("image")));
                    c.setComm_name(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_NAME)));
                    c.setMerchant_name(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_MER_NAME)));
                    c.setComm_types(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_TYPES)));
                    c.setComm_price(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_PRICE)));
                    c.setComm_phone(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_PHONE)));
                    c.setComm_time(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_TIME)));
                    c.setComm_des(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_DES)));
                    c.setStatus(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_STATUS)));
                    list.add(c);
                }
                cursor.close();
            } else {
                System.out.println("获取商品信息出现错误!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Comm> CommSearch(String name) {             //用户通过商品名查找商品信息
        List<Comm> list = new ArrayList<Comm>();
        try {
            Cursor cursor = sqLiteDatabase.rawQuery("select * from " + CommOrder.DATABASE_TABLE + " where " + CommOrder.NOTEPAD_NAME + " like ?", new String[]{"%" + name + "%"}, null);
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    Comm c = new Comm();
                    String id = cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_ID));
                    int ids = Integer.valueOf(id);
                    c.setId(ids);
                    c.setImage(cursor.getBlob(cursor.getColumnIndex("image")));
                    c.setComm_name(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_NAME)));
                    c.setMerchant_name(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_MER_NAME)));
                    c.setComm_types(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_TYPES)));
                    c.setComm_price(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_PRICE)));
                    c.setComm_phone(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_PHONE)));
                    c.setComm_time(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_TIME)));
                    c.setComm_des(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_DES)));
                    c.setStatus(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_STATUS)));
                    list.add(c);
                }
                cursor.close();
            } else {
                System.out.println("搜索商品信息出现错误!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Comm> CommTypesQuery(String types) {             //商家通过商家名查找商品信息
        List<Comm> list = new ArrayList<Comm>();
        try {
            Cursor cursor = sqLiteDatabase.rawQuery("select * from " + CommOrder.DATABASE_TABLE + " where " + CommOrder.NOTEPAD_TYPES + " = ?", new String[]{ types }, null);
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    Comm c = new Comm();
                    String id = cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_ID));
                    int ids = Integer.valueOf(id);
                    c.setId(ids);
                    c.setImage(cursor.getBlob(cursor.getColumnIndex("image")));
                    c.setComm_name(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_NAME)));
                    c.setMerchant_name(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_MER_NAME)));
                    c.setComm_types(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_TYPES)));
                    c.setComm_price(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_PRICE)));
                    c.setComm_phone(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_PHONE)));
                    c.setComm_time(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_TIME)));
                    c.setComm_des(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_DES)));
                    c.setStatus(cursor.getString(cursor.getColumnIndex(CommOrder.NOTEPAD_STATUS)));
                    list.add(c);
                }
                cursor.close();
            } else {
                System.out.println("获取商品信息出现错误!!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public byte[] CommBuy(String name){
        byte[] bytes = null;
        try{
            String sql = "select * from " + CommOrder.DATABASE_TABLE + " where " + CommOrder.NOTEPAD_NAME + " = ?";
            String[] strings = new String[]{name};
            Cursor cursor = sqLiteDatabase.rawQuery(sql,strings);
            bytes = cursor.getBlob(cursor.getColumnIndex("image"));
        }catch (Exception e){
            e.printStackTrace();
        }
        return bytes;
    }

    public boolean updateData(Comm c) {
        ContentValues values = new ContentValues();
        values.put(CommOrder.NOTEPAD_NAME, c.getComm_name());
        values.put(CommOrder.NOTEPAD_TYPES, c.getComm_types());
        values.put(CommOrder.NOTEPAD_PRICE, c.getComm_price());
        values.put(CommOrder.NOTEPAD_DES, c.getComm_des());
        values.put(CommOrder.NOTEPAD_STATUS,c.getStatus());
        String sql = CommOrder.NOTEPAD_NAME + "=?";
        String[] strings = new String[]{String.valueOf(c.getComm_name())};
        return
                sqLiteDatabase.update(CommOrder.DATABASE_TABLE, values, sql, strings) > 0;
    }

    public boolean deleteData(String name) {
        String sql = CommOrder.NOTEPAD_NAME + "=?";
        String[] va = new String[]{String.valueOf(name)};
        return
                sqLiteDatabase.delete(CommOrder.DATABASE_TABLE, sql, va) > 0;
    }
}
